package com.wjzjq.hiring.company.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wjzjq.hiring.common.DBObject;
import com.wjzjq.hiring.common.PageModel;
import com.wjzjq.hiring.common.util.DBUtil;
import com.wjzjq.hiring.company.entity.PositionEntity;

public class PositionDao {
	public List<PositionEntity> listAllPosition() {
		List<PositionEntity> positionList = new ArrayList<PositionEntity>();
		String sql = "select p.id as 'position_id',c.name as 'company_name',c.address,c.slogan"
				+ ",p.name as 'position_name',p.salary1,p.salary2,p.release_time from position p,company "
				+ "c where p.company_id=c.id";
		DBObject dbObject = DBUtil.executeQuery(sql);
		ResultSet resultSet = dbObject.getResultSet();
		try {
			while (resultSet.next()) {
				PositionEntity position = new PositionEntity();
				position.setId(resultSet.getInt("position_id"));
				position.setCompanyAddress(resultSet.getString("address"));
				position.setCompanyName(resultSet.getString("company_name"));
				position.setCompanySlogan(resultSet.getString("c.slogan"));
				position.setName(resultSet.getString("position_name"));
				position.setSalary1(resultSet.getInt("salary1"));
				position.setSalary2(resultSet.getInt("salary2"));
				// resultSet.getDate() 数据库存的是有格式的
				position.setReleaseTime(resultSet.getTimestamp("release_time"));
				positionList.add(position);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DBUtil.close(dbObject);
		return positionList;

	}

	public PositionEntity getPE(int pId) {
		PositionEntity pe = null;
		DBObject dbObject = null;
		String sql = "select p.name as 'position_name',p.duty,p.ability,p.salary1,p.salary2,p.release_time,c.name as 'company_name',"
				+ "c.linkman,c.phone,c.email,c.numbers,c.slogan,c.address from position p,company c where p.company_id=c.id and p.id=?";
		dbObject = DBUtil.executeQuery(sql, pId);
		ResultSet resultSet = dbObject.getResultSet();
		try {
			while (resultSet.next()) {
				pe = new PositionEntity();
				pe.setId(pId);
				pe.setCompanyLinkman(resultSet.getString("linkman"));
				pe.setCompanyAddress(resultSet.getString("address"));
				pe.setCompanyName(resultSet.getString("company_name"));
				pe.setDuty(resultSet.getString("duty"));
				pe.setAbility(resultSet.getString("ability"));
				pe.setCompanySlogan(resultSet.getString("c.slogan"));
				pe.setName(resultSet.getString("position_name"));
				pe.setSalary1(resultSet.getInt("salary1"));
				pe.setSalary2(resultSet.getInt("salary2"));
				pe.setCompanyEmail(resultSet.getString("email"));
				pe.setCompanyNumbers(resultSet.getString("numbers"));
				pe.setCompanyPhone(resultSet.getString("phone"));
				// resultSet.getDate() 数据库存的是有格式的
				pe.setReleaseTime(resultSet.getTimestamp("release_time"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(dbObject);
		}
		return pe;
	}

	public int deletePosition(int positionId) {
		// TODO Auto-generated method stub
		String sql = "delete from position where id=?";
		int row = DBUtil.executeUpdate(sql, positionId);
		return row;
	}

	public int addPosition(PositionEntity pe) {
		int row = 0;
		String sql = "insert into position values(null,?,?,?,?,?,?,NOW())";
		List<Object> paramList = new ArrayList();
		paramList.add(pe.getCompanyId());
		paramList.add(pe.getName());
		paramList.add(pe.getDuty());
		paramList.add(pe.getAbility());
		paramList.add(pe.getSalary1());
		paramList.add(pe.getSalary2());
		row = DBUtil.executeUpdate(sql, paramList);
		return row;
	}

	public int updatePosition(PositionEntity pe) {
		int row = 0;
		String sql = "update position set name=?,duty=?,ability=?,salary1=?,salary2=?,release_time=now() where id=?";
		List<Object> paramList = new ArrayList();
		paramList.add(pe.getName());
		paramList.add(pe.getDuty());
		paramList.add(pe.getAbility());
		paramList.add(pe.getSalary1());
		paramList.add(pe.getSalary2());
		paramList.add(pe.getId());
		row = DBUtil.executeUpdate(sql, paramList);
		return row;
	}

	public static PageModel<PositionEntity> getAppList(List<PositionEntity> positionList, int pageNum, int pageSum) {
		// TODO Auto-generated method stub
		String sql = "select p.duty,p.ability,c.id as 'company_id',p.id,c.name as 'company_name',p.name,p.salary1,p.salary2,p.release_time from position p,company c where c.id=p.company_id order by p.release_time desc limit ?, ?";
		// 条数从0开始 要显示当前页面的第一条就是 当前页面-1乘页面条数总数
		DBObject dbObject = DBUtil.executeQuery(sql, (pageNum - 1) * pageSum, pageSum);
		ResultSet rs = dbObject.getResultSet();
		List<PositionEntity> appList = new ArrayList<PositionEntity>();
		PositionEntity app = null;
		try {
			while (rs.next()) {
				app = new PositionEntity();
				app.setId(rs.getInt("id"));
				app.setReleaseTime(rs.getTimestamp("release_time"));
				app.setDuty(rs.getString("duty"));
				app.setAbility(rs.getString("ability"));
				app.setName(rs.getString("name"));
				app.setSalary1(rs.getInt("salary1"));
				app.setSalary2(rs.getInt("salary2"));
				app.setCompanyName(rs.getString("company_name"));
				app.setCompanyId(rs.getInt("company_id"));
				appList.add(app);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(dbObject);
		}
		return new PageModel<PositionEntity>(appList, positionList.size(), pageNum, pageSum);
	}
	
	public static PageModel<PositionEntity> getAppListp(int companyId,List<PositionEntity> positionList, int pageNum, int pageSum) {
		// TODO Auto-generated method stub
		String sql = "select p.duty,p.ability,c.id as 'company_id',p.id,c.name as 'company_name',p.name,p.salary1,p.salary2,p.release_time from position p,company c where c.id=p.company_id and p.company_id=? order by p.release_time desc limit ?, ?";
		// 条数从0开始 要显示当前页面的第一条就是 当前页面-1乘页面条数总数
		DBObject dbObject = DBUtil.executeQuery(sql,companyId, (pageNum - 1) * pageSum, pageSum);
		ResultSet rs = dbObject.getResultSet();
		List<PositionEntity> appList = new ArrayList<PositionEntity>();
		PositionEntity app = null;
		try {
			while (rs.next()) {
				app = new PositionEntity();
				app.setId(rs.getInt("id"));
				app.setReleaseTime(rs.getTimestamp("release_time"));
				app.setDuty(rs.getString("duty"));
				app.setAbility(rs.getString("ability"));
				app.setName(rs.getString("name"));
				app.setSalary1(rs.getInt("salary1"));
				app.setSalary2(rs.getInt("salary2"));
				app.setCompanyName(rs.getString("company_name"));
				app.setCompanyId(rs.getInt("company_id"));
				appList.add(app);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(dbObject);
		}
		return new PageModel<PositionEntity>(appList, positionList.size(), pageNum, pageSum);
	}
}
